/******************************************************************************************
Do-file:		Histograms.do
Date:			24th October 2019
Author:			Jennifer Mayo
*******************************************************************************************/


clear
set more off

global name "jmp4k"
global home "/Users/$name/Library/CloudStorage/OneDrive-UniversityofMissouri/Projects/Navigating the notches"
global data "$home/Replication"
global output "$home/CN Output"

use "$data/Overall score.dta", clear
duplicates drop
set scheme s1mono

************************************************************************

******************
**** FIGURE 3 ****
******************

hist score if PublicationYear!=2011 & PublicationYear!=2016, freq start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100)

graph export "$output/Figure3.eps", replace


******************
**** FIGURE 4 ****
******************

hist score if PublicationYear==2016 & justone==1, freq start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title(2016) graphregion(color(white)) note("The dashed lines represent the star thresholds") xlabel(10(10)100) name(Year2016freq) 
hist score if PublicationYear==2017 & justone==1, freq start(18.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title(2017) graphregion(color(white)) note("The dashed lines represent the star thresholds") xlabel(10(10)100) name(Year2017freq) 
hist score if PublicationYear==2018 & justone==1, freq start(18.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title(2018) graphregion(color(white)) note("The dashed lines represent the star thresholds") xlabel(10(10)100) name(Year2018freq) 
hist score if PublicationYear==2019 & justone==1, freq start(21.4) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title(2019) graphregion(color(white)) note("The dashed lines represent the star thresholds") xlabel(10(10)100) name(Year2019freq) 

graph combine Year2016freq Year2017freq Year2018freq Year2019freq, ///
rows(2) cols(2) graphregion(color(white)) iscale(0.5)
graph export "$output/Figure4.eps", replace


*******************
**** FIGURE B1 ****
*******************

merge m:m ein ratingid using "$data/Fin and A&T scores.dta", keepusing(finscore accountscore)
drop if _merge==2
drop _merge  

drop justone

hist finscore if PublicationYear!=2016 & PublicationYear!=2011, freq width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) title("Financial Health score (2002-2019)") xtitle("Raw score") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100)
graph export "$output/FigureB1a.eps", replace

hist accountscore, freq start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) title("A&T score (2011-2019)") xtitle("Raw score") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100) ylabel(,format(%9.0fc))
graph export "$output/FigureB1b.eps", replace


*******************
**** FIGURE B2 ****
*******************

use "$data/FinancialMetrics.dta", clear

hist administrationexpensesratio if PublicationYear!=2011 & PublicationYear!=2016 & (causeid == 18 | causeid==30), freq width(0.002) graphregion(color(white)) xline(0.031 0.051 0.101 0.151, lpattern(dash) lstyle(foreground)) xtitle("Admin Expense Percentage") note("Note: The dashed lines represent the points thresholds") // Food and Humanitarian Relief
graph export "$output/FigureB2a.eps", replace


hist administrationexpensesratio if administrationexpensesratio<0.5 & administrationexpensesratio>=0 & PublicationYear!=2011 & PublicationYear!=2016 & (causeid ==29 | causeid==28 | causeid==27 | causeid==15), freq width(0.01) graphregion(color(white)) xline(0.16 0.21 0.26 0.31, lpattern(dash) lstyle(foreground)) xtitle("Admin Expense Percentage") note("Note: The dashed lines represent the points thresholds") // Housing and Human Services
graph export "$output/FigureB2b.eps", replace


****************************
**** APPENDIX FIGURE D1 ****
****************************
 
use "$data/Overall score.dta", clear
 
merge m:m ein ratingid using "$data/Form990objects.dta", keepusing(totalrevenue administrativeexpenses fundraisingexpenses programexpenses totalexpenses totalcontributions totalnetassets)
drop if _merge==2
drop _merge 

rename PublicationYear year
merge m:m year using "$data/CPI.dta", keepusing(annualcpi)
drop if _merge==2
drop _merge 
rename year PublicationYear
** Make 2010 the base year
gen cpitemp = annualcpi if PublicationYear==2010
egen cpitemp2 = max(cpitemp)
gen cpideflate = annualcpi/cpitemp2

gen totalrevenue_deflated = totalrevenue*cpideflate

sort ein  PublicationYear
by ein: gen oneperEIN = _N ==_n

sort ntee_centile ein  PublicationYear
gen totalrevenue_deflated_million = totalrevenue_deflated/1000000

gen temptotalrev = totalrevenue_deflated_million if oneperEIN==1

by ntee_centile: egen totalrev_perntee_cent = sum(temptotalrev)
gen shareofrev_perntee_cent = temptotalrev/totalrev_perntee_cent
gen sharesquared = shareofrev_perntee_cent*shareofrev_perntee_cent

by ntee_centile: egen hhi_perntee_cent = sum(sharesquared)

hist score if PublicationYear!=2011 & PublicationYear!=2016 & hhi<0.036, start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title("10th percentile market concentration") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100) 
graph export "$output/FigureD1a.eps", replace

hist score if PublicationYear!=2011 & PublicationYear!=2016 & hhi>0.5, start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title("90th percentile market concentration") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100) 
graph export "$output/FigureD1b.eps", replace


****************************
**** APPENDIX FIGURE D2 ****
****************************

gen netassets_deflated = totalnetassets*cpideflate
drop cpitemp* cpideflate
sum netassets_deflated if PublicationYear>=2017 , det

* 10th percentile = 798,634 total net assets deflated; 90th percentile = $4.75million

twoway (hist score if PublicationYear!=2011 & PublicationYear!=2016 & netassets_deflated<800000, freq start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds" "Small charities are those with net assets less than $800,000 (10th percentile)" "Large charities are those with net assets greater than $47.5 million (90th percentile)") xlabel(10(10)100)) ///
(hist score if PublicationYear!=2011 & PublicationYear!=2016 & totalnetassets>47500000, freq start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) fcolor(none) lcolor(black) xtitle("Raw score") graphregion(color(white)) note("Notes: The dashed lines represent the star thresholds" "Small charities are those with net assets less than $800,000 (10th percentile)" "Large charities are those with net assets greater than $47.5 million (90th percentile)") xlabel(10(10)100)), legend(order(1 "Small charities" 2 "Large charities") ring(0) pos(9) cols(1) size(*0.6) rowgap(*0.1) symxsize(*0.6) region(style(none)))

graph export "$output/FigureD2.eps", replace


****************************
**** APPENDIX FIGURE D3 ****
****************************

gen cont_totalrev = totalcontributions/totalrevenue 
sum cont_totalrev if cont_totalrev<=1 & totalrevenue>=0 & totalcontributions>=0, det // 10th percentile = 42%; 90th = 99% 

hist score if PublicationYear!=2011 & PublicationYear!=2016 & cont_totalrev<0.42 & cont_totalrev<=1 & totalrevenue>=0 & totalcontributions>=0, start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title("10th percentile contribution reliance") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100) 
graph export "$output/FigureD3a.eps", replace

hist score if PublicationYear!=2011 & PublicationYear!=2016 & cont_totalrev>0.99 & cont_totalrev<=1 & totalrevenue>=0 & totalcontributions>=0, start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title("90th percentile contribution reliance") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100) 
graph export "$output/FigureD3b.eps", replace

****************************
**** APPENDIX FIGURE D4 ****
****************************

merge m:m ein PublicationYear PublicationMonth using "$data/Form990efile2010_17.dta", ///
keepusing(taxprep)   // only have this info for e-filers 2010-2017
drop if _merge==2
drop _merge   


bys taxprep: gen numberofaccounts = _N
replace numberofaccounts = . if taxprep==""

** Any preparer
hist score if PublicationYear>2016 & taxprep!="" & taxprep!="NA", freq  start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) title("Preparer") xtitle("Raw score") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds")  xlabel(10(10)100) name(score2017_19taxprep)

** Charities with no preparer
hist score if PublicationYear>2016 & taxprep=="NA", freq  start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) title("No Preparer") xtitle("Raw score") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100) name(score2017_19notax)

** Accountants that prepare a lot of charity accounts
hist score if PublicationYear>2016 & taxprep!="" & numberofaccounts>100 & taxprep!="NA", freq  start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title("Experienced tax preparer") graphregion(color(white)) note("Notes: The dashed lines represent the star thresholds" "An experienced preparer is one that has prepared accounts >100 times for rated charities") xlabel(10(10)100) name(score2017_19tax)

** Preparers with CPA certificate 
gen taxprepcpa = substr(taxprep,-3,3)

hist score if PublicationYear>2016 & taxprepcpa=="CPA", freq  start(17.0) width(1) xline(90 80 70 55, lpattern(dash) lstyle(foreground)) xtitle("Raw score") title("CPA certified preparer") graphregion(color(white)) note("Note: The dashed lines represent the star thresholds") xlabel(10(10)100) name(score2017_19cpa)

graph combine score2017_19taxprep score2017_19notax score2017_19tax score2017_19cpa, ///
rows(2) cols(2) graphregion(color(white)) iscale(0.5)

graph export "$output/FigureD4.eps", replace




